<?php

namespace MLM\Model\Member\Account;

use MLM\Model\Member\Account;

class Storage extends \MLM\Model\Generic\Db\Storage{
    
    public function save(Account $account){
        $insert = $this->_sql->insert()
                ->into('accounts')
                ->values($account->pull() );
        
        return $this->execute($insert);
    }

    public function updateAccount($data, $where){


        $update = $this->_sql->update();
        
        $update->table('accounts');
        
        $update->set($data);
        
        $update->where($where);

        
        
        return $this->execute($update);
    }

    public function getBankList(){
        $sql = "select *
                from bank order by bank_name";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute();

        return $res;
    }

    public function getAccountBank($memberId) {

       
         $sql = "select x.*, b.bank_name, b.bank_logo from (
                select *
                from accounts
                where member_id = ? 
                ) x left outer join bank b on x.bank_id = b.bank_id";


        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }


    public function getAccountDetail($memberId){
        $sql = "select *
                from accounts
                where member_id = ? ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }


    public function getAccountDetailByAcc($accNum){
        $sql = "select *
                from accounts
                where account_num = ? ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($accNum));

        return $res;
    }


   public function getTotalBalance($key) {

        $sql = "select amount, FORMAT(amount,0) as amount2,
                amount_1, FORMAT(amount_1,0) as amount_12,
                amount_2, amount_3, amount_4
                from accounts
                where account_num = ? ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));

        return $res;
    }

    public function getMemberAccountBank($memberId) {

        $sql = "select bank_acc_no, concat('[',bank_acc_no,']',' ',bank_name,' ','(',account_name,')') as bank_acc_num
                from accounts a, bank b
                where b.bank_id = a.bank_id and a.member_id = ?
                order by bank_acc_no";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }

    public function getTotalSaldoByPeriod($memberId, $accNum, $transType, $date1, $date2) {


        $sql = "select FORMAT(sum(amount), 2) as amount
                from accounts ";

        if ($accNum != ""){
            $sql .= " where account_num = '$accNum' ";
        }

        $stmt = $this->_db->query($sql);
        $resultA = $stmt->execute();

        $data = array();
        while ($resultA->next()) {
                $res = $resultA->current();
                $data['saldo'] = $res['amount'];
        }


        $sql = "select FORMAT(sum(amount1),2) as amt, debkred from (
                select trans_dtm, account_num1, FORMAT(amount1,2) as amt_rp, amount1,
                notes_txt as descr,
                mutation_type as debkred
                from v_transaction
                where (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";

        if ($accNum != ""){
            $sql .= " and account_num1 = '$accNum' ";
        }
        
        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        $sql .= "  
                ) ww group by debkred";

   

        $stmt = $this->_db->query($sql);
        $resultB = $stmt->execute(array($accNum));

        $data['debet'] = 0;
        $data['kredit'] = 0;

        while ($resultB->next()) {
                $res = $resultB->current();
                
                if ($res['debkred'] == 'K'){
                    $data['kredit'] = $res['amt'];
                }else{
                    $data['debet'] = $res['amt'];
                }
        }

        return $data;

    }


    public function getTotalSaldoMember() {


        $sql = "select sum(amount) as amount,
                sum(amount_1) as amount_1,
                sum(amount_2) as amount_2,
                sum(amount_3) as amount_3,
                sum(amount_4) as amount_4
                from accounts ";
        

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $data = array();
        while ($result->next()) {
                $res = $result->current();
                $data['saldo'] = $res['amount'];
                $data['amount1'] = $res['amount_1'];
                $data['amount2'] = $res['amount_2'];
                $data['amount3'] = $res['amount_3'];
                $data['amount4'] = $res['amount_4'];
        }

       return $data;

    }


    public function getTotalWalletByPeriod($memberId, $accNum, $wallet, $date1, $date2) {

        $data = array();

        $sql = "select sum(amount1) as amt, debkred from (
                select trans_dtm, account_num1, FORMAT(amount1,2) as amt_rp, amount1,
                notes_txt as descr,
                mutation_type as debkred
                from v_transaction
                where (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";

        if ($accNum != ""){
            $sql .= " and account_num1 = '$accNum' ";
        }

        if ($wallet == "E"){
            $sql .= " and ((trans_type_code in ('T008', 'T009', 'T010', 'T011') and mutation_type = 'K') or trans_type_code = 'T012') ";
        }else if  ($wallet == "REF"){
            $sql .= " and ((trans_type_code in ('T009') and mutation_type = 'D') or (trans_type_code = 'Q200' and amount2 = 2))";
        }else if  ($wallet == "ROI"){
            $sql .= " and ((trans_type_code = 'T008' and mutation_type = 'D') or trans_type_code = 'Q201')";
        }else if  ($wallet == "BIN"){
            $sql .= " and ((trans_type_code = 'T010' and mutation_type = 'D') or trans_type_code = 'Q202')";
        }else if  ($wallet == "SHOP"){
            $sql .= " and ((trans_type_code = 'T011' and mutation_type = 'D') or (trans_type_code = 'Q200' and amount2 = 4))";
        }

        $sql .= "
                ) ww group by debkred";



        $stmt = $this->_db->query($sql);
        $resultB = $stmt->execute(array($accNum));

        $data['debet'] = 0;
        $data['kredit'] = 0;

        while ($resultB->next()) {
                $res = $resultB->current();

                if ($res['debkred'] == 'K'){
                    $data['kredit'] = $res['amt'];
                }else{
                    $data['debet'] = $res['amt'];
                }
        }

        return $data;

    }

    
    
    
}
